<?php
class Register_model extends MY_Model 
{
	
	public $table = 'registers';
	
	public $side = array(
		'1' => 'บวก',
		'2' => 'หัก',
	);
	
	public $sub = array(
		'1' => array(
			'1' => 'รับโอนจากส่วนกลาง',
			'2' => 'รับรายได้',
			'3' => 'รับคืน',
			'4' => 'รวมรายรับ',
		),
		'2' => array(
			'4' => 'เงินโอนกลับส่วนกลาง',
			'5' => 'จ่ายค่าใช้จ่าย',
			'3' => 'รวมรายจ่าย'
		)
	);
	
	public $col_not_price = array(12,14,15,17,19,20,32,34,35,37,39,40,47,49,50);
	
    function __construct()
    {
        parent::__construct();
    }
	
	public function get_register($fund_id, $agency_id, $year = NULL, $month = NULL)
	{
		$year_budget = date_budget($year.'-'.$month.'-1');
		$sql = "SELECT REGISTERS.*, REGISTER_TITLES.TITLE 
		FROM REGISTERS
		JOIN REGISTER_TITLES ON REGISTER_TITLES.ID = REGISTERS.TITLE_ID
		WHERE REGISTERS.FUND_ID = $fund_id
		AND REGISTERS.AGENCY_ID = $agency_id
		AND REGISTERS.DATE_RECORD BETWEEN '$year_budget[0]' AND '$year_budget[1]'
		AND MONTH(REGISTERS.DATE_RECORD) = $month
		ORDER BY REGISTERS.DATE_RECORD ASC, REGISTERS.ID ASC";
		return $this->get($sql);
	}
	
	public function get_sum($fund_id, $agency_id, $year, $month, $fix_month = FALSE)
	{
		$year_budget = date_budget($year.'-'.$month.'-1');
		if($fix_month)
		{
			//list($y, $m, $d) = explode('-', $year);
			$end_year = $year.'-'.$month.'-30';
		}
		else
		{
			$end_year = $year_budget[1];
		} 
		$sql = 'SELECT  COL, SUM(PRICE) AS SUB_COL_PRICE FROM REGISTER_DETAILS JOIN REGISTERS ON REGISTERS.ID = REGISTER_DETAILS.REGISTER_ID';
		$sql .= ' WHERE REGISTERS.FUND_ID = '.$fund_id;
		$sql .= ' AND REGISTERS.AGENCY_ID = '.$agency_id; 
		$sql .= ' AND DATE_RECORD BETWEEN \''.$year_budget[0].'\' AND \''.$end_year.'\'';
		$sql .= $fix_month ? '' : ' AND MONTH(DATE_RECORD) = '.$month;
		$sql .= ' GROUP BY COL';
		return $this->db->getassoc($sql);
	}
	
	public function get_balance($fund_id, $agency_id, $year, $month )
	{
		//$this->db->debug = true;
		//$year_budget = year_budget($year);
		$sql = "SELECT REGISTERS.SIDE, SUM(REGISTER_DETAILS.PRICE) AS SUM_PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTER_DETAILS.IS_PRICE = 1
		AND REGISTERS.FUND_ID = $fund_id
		AND REGISTERS.AGENCY_ID = $agency_id
		AND YEAR(REGISTERS.DATE_RECORD) = '$year'
		AND MONTH(REGISTERS.DATE_RECORD) = $month
		GROUP BY REGISTERS.SIDE";
		$balance = $this->db->getassoc($sql);
		if($balance)
		return $balance[1] - $balance[2];
		else
		return NULL;
	}
	
	public function income_expenses($fund_id, $agency_id, $year, $month)
	{
		$year -= 543;
		$fix_year = fix_budget($year, $month);
		// พมจ รายงานรับ - จ่ายเงิน
		$sql = "SELECT REGISTER_TITLES.ID, REGISTER_TITLES.COL_TITLE, REGISTER_TITLES.SIDE, REGISTER_TITLES.SUB, DETAILS.COL, DETAILS.TOTAL_PRICE, SUMYEAR.YEAR_PRICE
		FROM REGISTER_TITLES
		LEFT JOIN (
			SELECT REGISTERS.TITLE_ID, REGISTER_DETAILS.COL, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = $fund_id
			AND REGISTERS.AGENCY_ID = $agency_id
			AND YEAR(REGISTERS.DATE_RECORD) = $year
			AND MONTH(REGISTERS.DATE_RECORD) = $month	
			GROUP BY REGISTER_DETAILS.COL, REGISTERS.TITLE_ID
			) AS DETAILS ON DETAILS.TITLE_ID = REGISTER_TITLES.ID
		LEFT JOIN (
			SELECT REGISTERS.TITLE_ID, SUM(REGISTER_DETAILS.PRICE) AS YEAR_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = $fund_id
			AND REGISTERS.AGENCY_ID = $agency_id
			AND REGISTERS.DATE_RECORD BETWEEN '".$fix_year[0]."' AND '".$fix_year[1]."' 
			AND REGISTER_DETAILS.IS_PRICE = 1	
			GROUP BY REGISTERS.TITLE_ID
			) AS SUMYEAR ON SUMYEAR.TITLE_ID = REGISTER_TITLES.ID
		ORDER BY REGISTER_TITLES.SIDE ASC, REGISTER_TITLES.SUB ASC, DETAILS.COL ASC";
		return $this->get($sql, FALSE, FALSE);
	}
	
	public function income_expenses_all($fund_id, $year, $month)
	{
		$year -= 543;
		$fix_year = fix_budget($year, $month);
		// พมจ รายงานรับ - จ่ายเงิน
		$sql = "SELECT REGISTER_TITLES.ID, REGISTER_TITLES.COL_TITLE, REGISTER_TITLES.SIDE, REGISTER_TITLES.SUB, DETAILS.COL, DETAILS.TOTAL_PRICE, SUMYEAR.YEAR_PRICE
		FROM REGISTER_TITLES
		LEFT JOIN (
			SELECT REGISTERS.TITLE_ID, REGISTER_DETAILS.COL, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = $fund_id 
			AND YEAR(REGISTERS.DATE_RECORD) = $year
			AND MONTH(REGISTERS.DATE_RECORD) = $month	
			GROUP BY REGISTER_DETAILS.COL, REGISTERS.TITLE_ID
			) AS DETAILS ON DETAILS.TITLE_ID = REGISTER_TITLES.ID
		LEFT JOIN (
			SELECT REGISTERS.TITLE_ID, SUM(REGISTER_DETAILS.PRICE) AS YEAR_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = $fund_id 
			AND REGISTERS.DATE_RECORD BETWEEN '".$fix_year[0]."' AND '".$fix_year[1]."' 
			AND REGISTER_DETAILS.IS_PRICE = 1	
			GROUP BY REGISTERS.TITLE_ID
			) AS SUMYEAR ON SUMYEAR.TITLE_ID = REGISTER_TITLES.ID
		ORDER BY REGISTER_TITLES.SIDE ASC, REGISTER_TITLES.SUB ASC, DETAILS.COL ASC";
		return $this->get($sql, FALSE, FALSE);
	}
	
}
?>